package com.hzsai.dao.Impl;

import com.hzsai.dao.UserDAO;
import com.hzsai.handler.BeanHandler;
import com.hzsai.handler.BeanListHandler;
import com.hzsai.pojo.User;
import com.hzsai.utils.SQLUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.*;
import java.util.List;

@Component
public class UserDAOImpl implements UserDAO{

    @Autowired
    @Qualifier("springbootDS")
    private DataSource dataSource = null;

    StringBuffer sql = new StringBuffer();

    public boolean insertUser(User user) {
        String sql;
        sql = "insert into aopuser(username, password, age) values(?,?,?)";
        Connection conn = null;
        PreparedStatement ps;

        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getPassword());
            ps.setInt(3, 23);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("执行SQL失败...");
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
                System.out.println("回退事务失败...");
            }
            return false;
        }

        return true;
    }

    public int queryUser(User user) {
        String sql = "";
        sql = "select * from aopuser where username=? and password=?";
        //sql = "insert into aopuser(username, password, age) values(?,?,?)";
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getPassword());
            System.out.println(ps);
            ps.executeQuery();
            ResultSet ret = ps.getResultSet();
            int rowCount = 0;
            while(ret.next()) {
                rowCount += 1;
                String username = ret.getString(1);
                String age      = ret.getString(2);
                String password = ret.getString(3);
                String note     = ret.getString(4);
                System.out.println(username + " " + age + " " + password + " " + note);
            }
            if (rowCount == ret.getFetchSize()) {
                return -1;
            } else {
                return ret.getFetchSize();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("执行SQL失败...");
        }

        return -1;
    }

    public int queryNumByUser(User user) {
        int count = 0;
        String sql = "";
        sql = "select count(0) from aopuser where username=? and password=?";
        //sql = "insert into aopuser(username, password, age) values(?,?,?)";
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getPassword());
            ps.executeQuery();
            ResultSet rs = ps.getResultSet();
            if (rs.next())
                count = rs.getInt(1);
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("SQL执行失败！！！");
        }

        return -1;
    }

    @Override
    public List<User> selectAllUser() throws SQLException {
        sql.setLength(0);

        sql.append("select * from aopuser;");

        List<User> retList = (List) SQLUtils.query(sql.toString(), null, new BeanListHandler(User.class));

        return retList;
    }

    /**
     * 在大并发的应用场景下，能用trigger吗？？？
     * @param current
     * @param size
     * @return
     * @throws SQLException
     */
    @Override
    public List<User> selectPagaUser(long current, long size) throws SQLException {

        sql.setLength(0);
        // 在数据库侧设置了delete, insert的trigger，直接从这个表中查数量
        sql.append("select count from aopusertriggercounter;");

        int totsize; // 总数据条数
        totsize = (Integer) SQLUtils.count(sql.toString(), null);

        // 总页数
        long totPage = Math.round(totsize / size);

        assert(current <= totPage);

        // 总条数中的第几条
        long offset = (current - 1) * size;

        sql.setLength(0);

        // 从mysql端分页处理
        sql.append("select * from aopuser where id >= (select id from aopuser limit ?, 1) limit ?;");

        Object[] params = {offset, size};

        List<User> retList = (List)SQLUtils.query(sql.toString(), params, new BeanListHandler(User.class));

        return retList;
    }

    @Override
    public User selectUserById(long id) throws SQLException {
        sql.setLength(0);
        sql.append("select * from aopuser where id = ?;");

        Object[] params = {id};

        User user = (User) SQLUtils.query(sql.toString(), params, new BeanHandler(User.class));

        return user;
    }
}
